<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="ch20941">CREATE TYPE</title><body><p id="sql_command_desc">Defines a new data type.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">CREATE TYPE <varname>name</varname> AS 
    ( <varname>attribute_name</varname> <varname>data_type</varname> [ COLLATE <varname>collation</varname> ] [, ... ] ] )

CREATE TYPE <varname>name</varname> AS ENUM 
    ( [ '<varname>label</varname>' [, ... ] ] )

CREATE TYPE <varname>name</varname> AS RANGE (
    SUBTYPE = <varname>subtype</varname>
    [ , SUBTYPE_OPCLASS = <varname>subtype_operator_class</varname> ]
    [ , COLLATION = <varname>collation</varname> ]
    [ , CANONICAL = <varname>canonical_function</varname> ]
    [ , SUBTYPE_DIFF = <varname>subtype_diff_function</varname> ]
)

CREATE TYPE <varname>name</varname> (
    INPUT = <varname>input_function</varname>,
    OUTPUT = <varname>output_function</varname>
    [, RECEIVE = <varname>receive_function</varname>]
    [, SEND = <varname>send_function</varname>]
    [, TYPMOD_IN = <varname>type_modifier_input_function</varname> ]
    [, TYPMOD_OUT = <varname>type_modifier_output_function</varname> ]
    [, INTERNALLENGTH = {<varname>internallength</varname> | VARIABLE}]
    [, PASSEDBYVALUE]
    [, ALIGNMENT = <varname>alignment</varname>]
    [, STORAGE = <varname>storage</varname>]
    [, LIKE = <varname>like_type</varname>
    [, CATEGORY = <varname>category</varname>]
    [, PREFERRED = <varname>preferred</varname>]
    [, DEFAULT = <varname>default</varname>]
    [, ELEMENT = <varname>element</varname>]
    [, DELIMITER = <varname>delimiter</varname>]
    [, COLLATABLE = <varname>collatable</varname>]
    [, COMPRESSTYPE = <varname>compression_type</varname>]
    [, COMPRESSLEVEL = <varname>compression_level</varname>]
    [, BLOCKSIZE = <varname>blocksize</varname>] )

CREATE TYPE <varname>name</varname></codeblock></section><section id="section3"><title>Description</title><p><codeph>CREATE TYPE</codeph> registers a new data type for use in the
current database. The user who defines a type becomes its owner. </p><p>If a schema name is given then the type is created in the specified
schema. Otherwise it is created in the current schema. The type name
must be distinct from the name of any existing type or domain in the
same schema. The type name must also be distinct from the name of any
existing table in the same schema.</p>
            <p>There are five forms of <codeph>CREATE TYPE</codeph>, as shown in the syntax synopsis
                above. They respectively create a <i>composite type</i>, an <i>enum type</i>, a <i>range
                    type</i>, a <i>base type</i>, or a <i>shell type</i>. The first four of these
                are discussed in turn below. A shell type is simply a placeholder for a type to be
                defined later; it is created by issuing <codeph>CREATE TYPE</codeph> with no
                parameters except for the type name. Shell types are needed as forward references
                when creating range types and base types, as discussed in those sections.</p><sectiondiv id="section4">
                <b>Composite Types</b>
                <p>The first form of <codeph>CREATE TYPE</codeph> creates a composite type. The
                    composite type is specified by a list of attribute names and data types. An
                    attribute's collation can be specified too, if its data type is collatable. A
                    composite type is essentially the same as the row type of a table, but using
                        <codeph>CREATE TYPE</codeph> avoids the need to create an actual table when
                    all that is wanted is to define a type. A stand-alone composite type is useful,
                    for example, as the argument or return type of a function.</p><p>To be able to
                    create a composite type, you must have <codeph>USAGE</codeph> privilege on all
                    attribute types.</p></sectiondiv>
            <sectiondiv id="enum"><b>Enumerated Types</b>
                <p>The second form of <codeph>CREATE TYPE</codeph> creates an enumerated
                        (<codeph>ENUM</codeph>) type, as described in <xref href="https://www.postgresql.org/docs/9.4/datatype-enum.html" format="html" scope="external">Enumerated Types</xref> in the PostgreSQL documentation.
                        <codeph>ENUM</codeph> types take a list of quoted labels, each of which must
                    be less than <codeph>NAMEDATALEN</codeph> bytes long (64 in a standard
                    build).</p><p>It is possible to create an enumerated type with zero labels, but
                    such a type cannot be used to hold values before at least one label is added
                    using <xref href="ALTER_TYPE.xml"/>.</p>
            </sectiondiv>
            <sectiondiv><b>Range Types</b><p>The third form of <codeph>CREATE TYPE</codeph> creates a
                    new range type, as described in <xref href="../datatype-range.xml#rangetypes"
                    />.</p><p>The range type's <varname>subtype</varname> can be any type with an
                    associated b-tree operator class (to determine the ordering of values for the
                    range type). Normally the subtype's default b-tree operator class is used to
                    determine ordering; to use a non-default operator class, specify its name with
                        <varname>subtype_opclass</varname>. If the subtype is collatable, and you
                    want to use a non-default collation in the range's ordering, specify the desired
                    collation with the <varname>collation</varname> option.</p><p>The optional
                        <varname>canonical</varname> function must take one argument of the range
                    type being defined, and return a value of the same type. This is used to convert
                    range values to a canonical form, when applicable. See Section <xref
                        href="../datatype-range.xml#rangetypes-defining"/> for more information.
                    Creating a <varname>canonical</varname> function is a bit tricky, since it must
                    be defined before the range type can be declared. To do this, you must first
                    create a shell type, which is a placeholder type that has no properties except a
                    name and an owner. This is done by issuing the command <codeph>CREATE TYPE
                            <varname>name</varname></codeph>, with no additional parameters. Then
                    the function can be declared using the shell type as argument and result, and
                    finally the range type can be declared using the same name. This automatically
                    replaces the shell type entry with a valid range type.</p><p>The optional
                        <varname>subtype_diff</varname> function must take two values of the
                        <varname>subtype</varname> type as argument, and return a double precision
                    value representing the difference between the two given values. While this is
                    optional, providing it allows much greater efficiency of GiST indexes on columns
                    of the range type. See <xref href="../datatype-range.xml#rangetypes-defining"/>
                    for more information.</p></sectiondiv>
<sectiondiv id="section5"><b>Base Types</b><p>The fourth form of <codeph>CREATE TYPE</codeph>
                    creates a new base type (scalar type). You must be a superuser to create a new
                    base type. The parameters may appear in any order, not only that shown in the
                    syntax, and most are optional. You must register two or more functions (using
                        <codeph>CREATE FUNCTION</codeph>) before defining the type. The support
                    functions <varname>input_function</varname> and
                        <varname>output_function</varname> are required, while the functions
                        <varname>receive_function</varname>, <varname>send_function</varname>,
                        <varname>type_modifier_input_function</varname>,
                        <varname>type_modifier_output_function</varname>, and
                        <varname>analyze_function</varname> are optional. Generally these functions
                    have to be coded in C or another low-level language. In Greenplum Database, any
                    function used to implement a data type must be defined as
                        <codeph>IMMUTABLE</codeph>.</p><p>The <varname>input_function</varname>
                    converts the type's external textual representation to the internal
                    representation used by the operators and functions defined for the type.
                        <varname>output_function</varname> performs the reverse transformation. The
                    input function may be declared as taking one argument of type
                        <codeph>cstring</codeph>, or as taking three arguments of types
                        <codeph>cstring</codeph>, <codeph>oid</codeph>, <codeph>integer</codeph>.
                    The first argument is the input text as a C string, the second argument is the
                    type's own OID (except for array types, which instead receive their element
                    type's OID), and the third is the <codeph>typmod</codeph> of the destination
                    column, if known (<codeph>-1</codeph> will be passed if not). The input function
                    must return a value of the data type itself. Usually, an input function should
                    be declared <codeph>STRICT</codeph>; if it is not, it will be called with a
                        <codeph>NULL</codeph> first parameter when reading a <codeph>NULL</codeph>
                    input value. The function must still return <codeph>NULL</codeph> in this case,
                    unless it raises an error. (This case is mainly meant to support domain input
                    functions, which may need to reject <codeph>NULL</codeph> inputs.) The output
                    function must be declared as taking one argument of the new data type. The
                    output function must return type <codeph>cstring</codeph>. Output functions are
                    not invoked for <codeph>NULL</codeph> values. </p><p>The optional
                        <varname>receive_function</varname> converts the type's external binary
                    representation to the internal representation. If this function is not supplied,
                    the type cannot participate in binary input. The binary representation should be
                    chosen to be cheap to convert to internal form, while being reasonably portable.
                    (For example, the standard integer data types use network byte order as the
                    external binary representation, while the internal representation is in the
                    machine's native byte order.) The receive function should perform adequate
                    checking to ensure that the value is valid. The receive function may be declared
                    as taking one argument of type <codeph>internal</codeph>, or as taking three
                    arguments of types <codeph>internal</codeph>, <codeph>oid</codeph>,
                        <codeph>integer</codeph>. The first argument is a pointer to a
                        <codeph>StringInfo</codeph> buffer holding the received byte string; the
                    optional arguments are the same as for the text input function. The receive
                    function must return a value of the data type itself. Usually, a receive
                    function should be declared <codeph>STRICT</codeph>; if it is not, it will be
                    called with a <codeph>NULL</codeph> first parameter when reading a NULL input
                    value. The function must still return <codeph>NULL</codeph> in this case, unless
                    it raises an error. (This case is mainly meant to support domain receive
                    functions, which may need to reject <codeph>NULL</codeph> inputs.) Similarly,
                    the optional <varname>send_function</varname> converts from the internal
                    representation to the external binary representation. If this function is not
                    supplied, the type cannot participate in binary output. The send function must
                    be declared as taking one argument of the new data type. The send function must
                    return type <codeph>bytea</codeph>. Send functions are not invoked for
                        <codeph>NULL</codeph> values. </p><p>The optional
                        <varname>type_modifier_input_function</varname> and
                        <varname>type_modifier_output_function</varname> are required if the type
                    supports modifiers. Modifiers are optional constraints attached to a type
                    declaration, such as <codeph>char(5)</codeph> or <codeph>numeric(30,2)</codeph>.
                    While Greenplum Database allows user-defined types to take one or more simple
                    constants or identifiers as modifiers, this information must fit into a single
                    non-negative integer value for storage in the system catalogs. Greenplum
                    Database passes the declared modifier(s) to the
                        <varname>type_modifier_input_function</varname> in the form of a
                        <codeph>cstring</codeph> array. The modifier input function must check the
                    values for validity, throwing an error if they are incorrect. If the values are
                    correct, the modifier input function returns a single non-negative integer value
                    that Greenplum Database stores as the column <codeph>typmod</codeph>. Type
                    modifiers are rejected if the type was not defined with a
                        <varname>type_modifier_input_function</varname>. The
                        <varname>type_modifier_output_function</varname> converts the internal
                    integer <codeph>typmod</codeph> value back to the correct form for user display.
                    The modifier output function must return a <codeph>cstring</codeph> value that
                    is the exact string to append to the type name. For example,
                        <codeph>numeric</codeph>'s function might return <codeph>(30,2)</codeph>.
                    The <varname>type_modifier_output_function</varname> is optional. When not
                    specified, the default display format is the stored <codeph>typmod</codeph>
                    integer value enclosed in parentheses.</p><p>You should at this point be
                    wondering how the input and output functions can be declared to have results or
                    arguments of the new type, when they have to be created before the new type can
                    be created. The answer is that the type should first be defined as a shell type,
                    which is a placeholder type that has no properties except a name and an owner.
                    This is done by issuing the command <codeph>CREATE TYPE
                        <varname>name</varname></codeph>, with no additional parameters. Then the
                    I/O functions can be defined referencing the shell type. Finally, <codeph>CREATE
                        TYPE</codeph> with a full definition replaces the shell entry with a
                    complete, valid type definition, after which the new type can be used normally.
                    </p><p>The <varname>like_type</varname> parameter provides an alternative method
                    for specifying the basic representation properties of a data type: copy them
                    from some existing type. The values <codeph>internallength</codeph>,
                        <codeph>passedbyvalue</codeph>, <codeph>alignment</codeph>, and
                        <codeph>storage</codeph> are copied from the named type. (It is possible,
                    though usually undesirable, to override some of these values by specifying them
                    along with the <codeph>LIKE</codeph> clause.) Specifying representation this way
                    is especially useful when the low-level implementation of the new type
                    "piggybacks" on an existing type in some fashion.</p><p>While the details of the
                    new type's internal representation are only known to the I/O functions and other
                    functions you create to work with the type, there are several properties of the
                    internal representation that must be declared to Greenplum Database. Foremost of
                    these is <varname>internallength</varname>. Base data types can be fixed-length,
                    in which case <varname>internallength</varname> is a positive integer, or
                    variable length, indicated by setting <varname>internallength</varname> to
                        <codeph>VARIABLE</codeph>. (Internally, this is represented by setting
                        <codeph>typlen</codeph> to <codeph>-1</codeph>.) The internal representation
                    of all variable-length types must start with a 4-byte integer giving the total
                    length of this value of the type.</p><p>The optional flag
                        <codeph>PASSEDBYVALUE</codeph> indicates that values of this data type are
                    passed by value, rather than by reference. You may not pass by value types whose
                    internal representation is larger than the size of the <codeph>Datum</codeph>
                    type (4 bytes on most machines, 8 bytes on a few). </p><p>The
                        <varname>alignment</varname> parameter specifies the storage alignment
                    required for the data type. The allowed values equate to alignment on 1, 2, 4,
                    or 8 byte boundaries. Note that variable-length types must have an alignment of
                    at least 4, since they necessarily contain an <codeph>int4</codeph> as their
                    first component. </p><p>The <varname>storage</varname> parameter allows
                    selection of storage strategies for variable-length data types. (Only
                        <codeph>plain</codeph> is allowed for fixed-length types.)
                        <codeph>plain</codeph> specifies that data of the type will always be stored
                    in-line and not compressed. <codeph>extended</codeph> specifies that the system
                    will first try to compress a long data value, and will move the value out of the
                    main table row if it's still too long. <codeph>external</codeph> allows the
                    value to be moved out of the main table, but the system will not try to compress
                    it. <codeph>main</codeph> allows compression, but discourages moving the value
                    out of the main table. (Data items with this storage strategy may still be moved
                    out of the main table if there is no other way to make a row fit, but they will
                    be kept in the main table preferentially over <codeph>extended</codeph> and
                        <codeph>external</codeph> items.) </p><p>A default value may be specified,
                    in case a user wants columns of the data type to default to something other than
                    the null value. Specify the default with the <codeph>DEFAULT</codeph> key word.
                    (Such a default may be overridden by an explicit <codeph>DEFAULT</codeph> clause
                    attached to a particular column.) </p><p>To indicate that a type is an array,
                    specify the type of the array elements using the <codeph>ELEMENT</codeph> key
                    word. For example, to define an array of 4-byte integers (int4), specify
                        <codeph>ELEMENT = int4</codeph>. More details about array types appear
                    below. </p><p>The <i>category</i> and <i>preferred</i> parameters can be used to
                    help control which implicit cast Greenplum Database applies in ambiguous
                    situations. Each data type belongs to a category named by a single ASCII
                    character, and each type is either "preferred" or not within its category. The
                    parser will prefer casting to preferred types (but only from other types within
                    the same category) when this rule helps resolve overloaded functions or
                    operators. For types that have no implicit casts to or from any other types, it
                    is sufficient to retain the default settings. However, for a group of related
                    types that have implicit casts, it is often helpful to mark them all as
                    belonging to a category and select one or two of the "most general" types as
                    being preferred within the category. The <i>category</i> parameter is especially
                    useful when you add a user-defined type to an existing built-in category, such
                    as the numeric or string types. It is also possible to create new
                    entirely-user-defined type categories. Select any ASCII character other than an
                    upper-case letter to name such a category.</p><p>To indicate the delimiter to be
                    used between values in the external representation of arrays of this type,
                        <codeph>delimiter</codeph> can be set to a specific character. The default
                    delimiter is the comma (,). Note that the delimiter is associated with the array
                    element type, not the array type itself.</p><p> If the optional Boolean
                    parameter <varname>collatable</varname> is true, column definitions and
                    expressions of the type may carry collation information through use of the
                        <codeph>COLLATE</codeph> clause. It is up to the implementations of the
                    functions operating on the type to actually make use of the collation
                    information; this does not happen automatically merely by marking the type
                    collatable. </p></sectiondiv><sectiondiv id="section6"><b>Array Types</b><p>Whenever a user-defined type is created, Greenplum
                    Database automatically creates an associated array type, whose name consists of
                    the element type's name prepended with an underscore, and truncated if necessary
                    to keep it less than <codeph>NAMEDATALEN</codeph> bytes long. (If the name so
                    generated collides with an existing type name, the process is repeated until a
                    non-colliding name is found.) This implicitly-created array type is variable
                    length and uses the built-in input and output functions
                        <codeph>array_in</codeph> and <codeph>array_out</codeph>. The array type
                    tracks any changes in its element type's owner or schema, and is dropped if the
                    element type is.</p><p>You might reasonably ask why there is an
                        <codeph>ELEMENT</codeph> option, if the system makes the correct array type
                    automatically. The only case where it's useful to use <codeph>ELEMENT</codeph>
                    is when you are making a fixed-length type that happens to be internally an
                    array of a number of identical things, and you want to allow these things to be
                    accessed directly by subscripting, in addition to whatever operations you plan
                    to provide for the type as a whole. For example, type <codeph>point</codeph> is
                    represented as just two floating-point numbers, each can be accessed using
                        <codeph>point[0]</codeph> and <codeph>point[1]</codeph>. Note that this
                    facility only works for fixed-length types whose internal form is exactly a
                    sequence of identical fixed-length fields. A subscriptable variable-length type
                    must have the generalized internal representation used by
                        <codeph>array_in</codeph> and <codeph>array_out</codeph>. For historical
                    reasons (i.e., this is clearly wrong but it's far too late to change it),
                    subscripting of fixed-length array types starts from zero, rather than from one
                    as for variable-length arrays.</p></sectiondiv></section><section id="section7"><title>Parameters</title>
            <parml>
                <plentry>
                    <pt><varname>name</varname></pt>
                    <pd>The name (optionally schema-qualified) of a type to be created. </pd>
                </plentry>
                <plentry>
                    <pt><varname>attribute_name</varname></pt>
                    <pd>The name of an attribute (column) for the composite type. </pd>
                </plentry>
                <plentry>
                    <pt><varname>data_type</varname></pt>
                    <pd>The name of an existing data type to become a column of the composite type.
                    </pd>
                </plentry>
                <plentry>
                    <pt><varname>collation</varname></pt>
                    <pd>The name of an existing collation to be associated with a column of a
                        composite type, or with a range type. </pd>
                </plentry>
                <plentry>
                    <pt><varname>label</varname></pt>
                    <pd>A string literal representing the textual label associated with one value of
                        an enum type.</pd>
                </plentry>
                <plentry>
                    <pt><varname>subtype</varname></pt>
                    <pd>The name of the element type that the range type will represent ranges
                        of.</pd>
                </plentry>
                <plentry>
                    <pt><varname>subtype_operator_class</varname></pt>
                    <pd>The name of a b-tree operator class for the subtype.</pd>
                </plentry>
                <plentry>
                    <pt><varname>canonical_function</varname></pt>
                    <pd>The name of the canonicalization function for the range type.</pd>
                </plentry>
                <plentry>
                    <pt><varname>subtype_diff_function</varname></pt>
                    <pd>The name of a difference function for the subtype.</pd>
                </plentry>
                <plentry>
                    <pt><varname>input_function</varname></pt>
                    <pd>The name of a function that converts data from the type's external textual
                        form to its internal form. </pd>
                </plentry>
                <plentry>
                    <pt><varname>output_function</varname></pt>
                    <pd>The name of a function that converts data from the type's internal form to
                        its external textual form. </pd>
                </plentry>
                <plentry>
                    <pt><varname>receive_function</varname></pt>
                    <pd>The name of a function that converts data from the type's external binary
                        form to its internal form. </pd>
                </plentry>
                <plentry>
                    <pt><varname>send_function</varname></pt>
                    <pd>The name of a function that converts data from the type's internal form to
                        its external binary form. </pd>
                </plentry>
                <plentry>
                    <pt><varname>type_modifier_input_function</varname></pt>
                    <pd>The name of a function that converts an array of modifier(s) for the type to
                        internal form.</pd>
                </plentry>
                <plentry>
                    <pt><varname>type_modifier_output_function</varname></pt>
                    <pd>The name of a function that converts the internal form of the type's
                        modifier(s) to external textual form.</pd>
                </plentry>
                <plentry>
                    <pt><varname>internallength</varname></pt>
                    <pd>A numeric constant that specifies the length in bytes of the new type's
                        internal representation. The default assumption is that it is
                        variable-length. </pd>
                </plentry>
                <plentry>
                    <pt><varname>alignment</varname></pt>
                    <pd>The storage alignment requirement of the data type. Must be one of
                            <codeph>char</codeph>, <codeph>int2</codeph>, <codeph>int4</codeph>, or
                            <codeph>double</codeph>. The default is <codeph>int4</codeph>. </pd>
                </plentry>
                <plentry>
                    <pt><varname>storage</varname></pt>
                    <pd>The storage strategy for the data type. Must be one of
                            <codeph>plain</codeph>, <codeph>external</codeph>,
                            <codeph>extended</codeph>, or <codeph>main</codeph>. The default is
                            <codeph>plain</codeph>. </pd>
                </plentry>
                <plentry>
                    <pt><varname>like_type</varname></pt>
                    <pd>The name of an existing data type that the new type will have the same
                        representation as. The values <varname>internallength</varname>,
                            <varname>passedbyvalue</varname>, <varname>alignment</varname>, and
                            <varname>storage</varname>, are copied from that type, unless overridden
                        by explicit specification elsewhere in this <codeph>CREATE TYPE</codeph>
                        command.</pd>
                </plentry>
                <plentry>
                    <pt><varname>category</varname></pt>
                    <pd>The category code (a single ASCII character) for this type. The default is
                            '<codeph>U</codeph>', signifying a user-defined type. You can find the
                        other standard category codes in <xref
                            href="../system_catalogs/pg_type.xml#topic1/typcategory" type="topic"
                            format="dita"><codeph>pg_type</codeph> Category Codes</xref>. You may
                        also assign unused ASCII characters to custom categories that you
                        create.</pd>
                </plentry>
                <plentry>
                    <pt><varname>preferred</varname></pt>
                    <pd><codeph>true</codeph> if this type is a preferred type within its type
                        category, else <codeph>false</codeph>. The default value is
                            <codeph>false</codeph>. Be careful when you create a new preferred type
                        within an existing type category; this could cause surprising behaviour
                        changes.</pd>
                </plentry>
                <plentry>
                    <pt><varname>default</varname></pt>
                    <pd>The default value for the data type. If this is omitted, the default is
                        null. </pd>
                </plentry>
                <plentry>
                    <pt><varname>element</varname></pt>
                    <pd>The type being created is an array; this specifies the type of the array
                        elements. </pd>
                </plentry>
                <plentry>
                    <pt><varname>delimiter</varname></pt>
                    <pd>The delimiter character to be used between values in arrays made of this
                        type.</pd>
                </plentry>
                <plentry>
                    <pt><varname>collatable</varname></pt>
                    <pd> True if this type's operations can use collation information. The default
                        is false. </pd>
                </plentry>
                <plentry>
                    <pt><varname>compression_type</varname></pt>
                    <pd>Set to <codeph>ZLIB</codeph> (the default), <codeph>ZSTD</codeph>,
                            <codeph>RLE_TYPE</codeph>, or <codeph>QUICKLZ</codeph><sup>1</sup> to
                        specify the type of compression used in columns of this type.<note
                            type="note"><sup>1</sup>QuickLZ compression is available only in the
                            commercial release of Tanzu Greenplum.</note>
                    </pd>
                </plentry>
                <plentry>
                    <pt><varname>compression_level</varname></pt>
                    <pd> For Zstd compression, set to an integer value from 1 (fastest compression)
                        to 19 (highest compression ratio). For zlib compression, the valid range is
                        from 1 to 9. The QuickLZ compression level can only be set to 1. For
                            <codeph>RLE_TYPE</codeph>, the compression level can be set to an
                        integer value from 1 (fastest compression) to 4 (highest compression ratio).
                        The default compression level is 1.</pd>
                </plentry>
                <plentry>
                    <pt><varname>blocksize</varname></pt>
                    <pd> Set to the size, in bytes, for each block in the column. The
                            <codeph>BLOCKSIZE</codeph> must be between 8192 and 2097152 bytes, and
                        be a multiple of 8192. The default block size is 32768.</pd>
                </plentry>
            </parml></section><section id="section8"><title>Notes</title><p>User-defined type names cannot begin with the underscore character (_)
and can only be 62 characters long (or in general <codeph>NAMEDATALEN
- 2</codeph>, rather than the <codeph>NAMEDATALEN - 1</codeph> characters
allowed for other names). Type names beginning with underscore are reserved
for internally-created array type names.</p>
<p>Greenplum Database does not support adding storage options for row or composite types.</p>
<p>Storage options defined at the table- and column- level override the default storage options defined for a scalar type.</p>
<p>Because there are no restrictions on use of a data type once it's been created, creating a base
                type or range type is tantamount to granting public execute permission on the
                functions mentioned in the type definition. (The creator of the type is therefore
                required to own these functions.) This is usually not an issue for the sorts of
                functions that are useful in a type definition. But you might want to think twice
                before designing a type in a way that would require 'secret' information to be used
                while converting it to or from external form. </p></section><section id="section9"><title>Examples</title><p>This example creates a composite type and uses it in a function definition:</p><codeblock>CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;</codeblock>
<p>This example creates the enumerated type <codeph>mood</codeph> and uses it in a table definition.</p> 
<codeblock>CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)
</codeblock>
            <p>This example creates a range
                type:<codeblock>CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);</codeblock></p>
<p>This example creates the base data type <codeph>box</codeph> and then uses the type in a table
                definition:</p><codeblock>CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS 
... ;

CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS 
... ;

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

CREATE TABLE myboxes (
    id integer,
    description box
);</codeblock><p>If the internal structure of <codeph>box</codeph> were an array of four <codeph>float4</codeph>
                elements, we might instead use:</p><codeblock>CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);</codeblock><p>which would allow a box value's component numbers to be accessed by
subscripting. Otherwise the type behaves the same as before.</p><p>This example creates a large object type and uses it in a table definition:</p><codeblock>CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);

CREATE TABLE big_objs (
    id integer,
    obj bigobj
    );</codeblock></section><section id="section10">
            <title>Compatibility</title>
            <p> The first form of the <codeph>CREATE TYPE</codeph> command, which creates a
                composite type, conforms to the SQL standard. The other forms are Greenplum Database
                extensions. The <codeph>CREATE TYPE</codeph> statement in the SQL standard also
                defines other forms that are not implemented in Greenplum Database.</p>
            <p> The ability to create a composite type with zero attributes is a Greenplum
                Database-specific deviation from the standard (analogous to the same case in
                    <codeph>CREATE TABLE</codeph>). </p>
    </section><section id="section11"><title>See Also</title>
            <p>
                <codeph><xref href="ALTER_TYPE.xml#topic1" type="topic" format="dita"/></codeph>,
                        <codeph><xref href="CREATE_DOMAIN.xml#topic1" type="topic" format="dita"
                    /></codeph>, <codeph><xref href="CREATE_FUNCTION.xml#topic1" type="topic"
                        format="dita"/></codeph>, <codeph><xref href="./DROP_TYPE.xml#topic1"
                        type="topic" format="dita"/></codeph></p></section></body></topic>
